/********************************************
name:			kayla freeman
date:			8/1/24
purpose:		prep common lender indicator, and lead lender intermed file
********************************************/

clear

/*the steps:
	1) get a file of leads
	2) for suppliers, get running list of months with each facilityid outstanding that month.
		1b) merge onto the leads data
	3) for customers, get running list of months wtih each facilityid outstanding that month.
		3b) merge onto the leads data
	4) merge the supp file and the cust file on date and s_gvkey; gen CL
	*/

/*1) get a file of leads*/
clear
use 00_leadshares
sort facilityid
gen lead = leadarrang=="Yes"
sort facilityid
bysort facilityid: gen num = _N
bysort facilityid: egen numlead = total(lead)

/*note: follow Bharath et al's approach for lead lender:
	1. if marked lead arranger, yes. 
	2. if agent, administrative agent, arranger, lead bank, yes.
	3. sole lender transaction, yes.
*/	

/*1 first*/
capture drop lead
gen lead = 0
replace lead = 1 if leadarrange=="Yes"
/*2 next*/
tab lenderrole /*so many...*/
replace lead = 1 if lenderrole=="Admin agent" 
replace lead = 1 if lenderrole=="Lead arranger" 
replace lead = 1 if lenderrole=="Lead bank"  
replace lead = 1 if lenderrole=="Mandated Lead arranger"  
replace lead = 1 if lenderrole=="Mandated arranger"  
replace lead = 1 if lenderrole=="Sole lender"


replace lead = 1 if lenderrole=="Agent"
replace lead = 1 if lenderrole=="Arranger"
replace lead = 1 if lenderrole=="Co-agent"
replace lead = 1 if lenderrole=="Co-arranger"
replace lead = 1 if lenderrole=="Co-lead arranger"
replace lead = 1 if lenderrole=="Facility agent"
replace lead = 1 if lenderrole=="Facility arranger"
replace lead = 1 if lenderrole=="Joint agent"
replace lead = 1 if lenderrole=="Joint arranger"
replace lead = 1 if lenderrole=="Senior arranger"
replace lead = 1 if lenderrole=="Senior co-arranger"


/*3 finally*/
gen altshare = bankalloc /*Note: Gatev & Strahan (JFE 2009) divide commitment equally across lenders when the lead share is missing*/
	bys facilityid: replace altshare = 100/_N if missing(bankalloc)
	replace bankalloc = altshare if missing(bankalloc) & altshare==100
keep if lead==1
bys facilityid: egen avgshare = mean(bankalloc)
by facilityid: egen altavgshare= mean(altshare)
keep facilityid companyid avgshare altavgshare
sort facilityid companyid
save "10_leads",replace /*intmed file needed for other Dos*/


/*for common lender - redo the running months as in Do2*/

/*prelim step 1: get the supplier gvkeys from pair data*/
clear
use 02_supply_chain_links
sort s_gvkey c_gvkey
bysort s_gvkey c_gvkey:keep if _n==1
keep s_gvkey c_gvkey
destring s_gvkey c_gvkey,replace
ren c_gvkey gvkey
tempfile scg
save `scg'

sort s_gvkey gvkey
bysort s_gvkey: keep if _n==1
keep s_gvkey
ren s_gvkey gvkey
tempfile sc
save `sc'

/*prelim step 2: get fyear end for supps. let's get an indicator for each month-cal-year combo from comp data, and then link to the start and end data for first year and last year.*/

clear
use 01_comp_basic_raw_vars
keep if indfmt=="INDL"
keep gvkey datadate fyear
sort gvkey fyear
drop if missing(fyear)
bysort gvkey fyear: gen dup = cond(_N==1,0,_n)
tab dup /*only 2; go ahead and create running months and keep all for each gvkey fyear combo*/
expand 12
sort gvkey fyear dup
bysort gvkey fyear dup: gen month = month(datadate)
bysort gvkey fyear dup: replace month = month[_n-1]-1 if _n>1
gen calyear = year(datadate)
replace calyear = calyear-1 if month<1
replace month = month+12 if month<1
gen date = mdy(month,1,calyear)
format date %td
drop datadate month calyear
drop dup
sort gvkey fyear date
bysort gvkey fyear date: gen dup = cond(_N==1,0,_n)
tab dup /*these are literally dups, so drop extras*/
drop if dup>1
drop dup
sort gvkey date
bysort gvkey date: gen dup = cond(_N==1,0,_n)
tab dup 
bysort gvkey date: gen fyearalt = fyear[2]
bysort gvkey date: keep if _n==1
drop dup
destring gvkey,replace
tempfile months /*file has gvkey fyear date fyearalt*/
save `months' /*will come back to this, use for both facstart and facend*/

/*merge into the DS data*/
clear
import excel using "DS_Links2018.xlsx", firstrow case(lower) sheet(link_data)
drop if year(facstartdate)>2016
/*drop unnec vars*/
ren discrep_bcoid_flag linkflag
drop company coname_h fic score* smbl* ticker discrep*
merge m:1 gvkey using `sc'
keep if _merge==3
drop _merge
ren facid facilityid
sort facilityid
tempfile inprog
save `inprog'
clear

use 00_facility 
gen year = substr(facilitystartdate,1,4)
destring year,replace
drop if year >2016
drop year

merge 1:1 facilityid using `inprog'  

keep if _merge==3
drop _merge

foreach var of varlist *{
     rename `var' `=lower("`var'")'
}

/*fix facilitystartdate*/
gen _year = substr(facilitystartdate,1,4)
gen _mo = substr(facilitystartdate,6,2)
gen _day = substr(facilitystartdate,9,2)
destring _year _mo _day,replace
gen altst = mdy(_mo,_day,_year)
format altst %td
drop facilitystartdate _year _mo _day
ren altst facilitystartdate
/*fix facilityenddate*/
gen _year = substr(facilityenddate,1,4)
gen _mo = substr(facilityenddate,6,2)
gen _day = substr(facilityenddate,9,2)
destring _year _mo _day,replace
gen altst = mdy(_mo,_day,_year)
format altst %td
drop facilityenddate _year _mo _day
ren altst facilityenddate

count if facstartdate!=facilitystartdate  
order facstartdate facilitystart /*keep earlier*/
replace facstartdate = facilitystartdate if facilitystartdate<facstartdate

/*drop unnecessary vars*/
drop bcoid   facilitystartdate   loantype    secured     
gen facstart = mdy(month(facstartdate),1,year(facstartdate)) /*first day of mo to match the fyr-end data*/
format facstart %td
drop facstartdate
gen facend = mdy(month(facilityenddate),1,year(facilityenddate)) /*first day of mo to match the fyr-end data*/
format facend %td
drop facilityenddate

ren facstart date
merge m:1 gvkey date using `months'
/*for _merge==1, let's make fyear equal to calyear*/
replace fyear = year(date) if _merge==1
drop if _merge==2
drop _merge
ren date facstart
ren fyear st_fyear
ren fyearalt st_fyearalt

/*before merging in for facend, address issue of missing facend: first, replace with maturity-suggested end, when possible. 
	when maturity is missing too, just let facend = facstart; that way it just gets counted in one year*/
count if missing(facend) & !missing(maturity) 
tab maturity if missing(facend) & !missing(maturity)
gen altend = mofd(facstart)+maturity
format altend %tm
order facstart facend maturity altend
gen altalt =dofm(altend)
format altalt %td
order altalt,after(altend)
replace altend = altalt
drop altalt
replace facend = altend if missing(facend) /*first replace with mat-implied*/
replace facend = facstart if missing(facend) /*next replace with facstart, a.n.*/
drop altend

ren facend date
merge m:1 gvkey date using `months'
replace fyear = year(date) if _merge==1
drop if _merge==2
drop _merge
ren date facend
ren fyear end_fyear
ren fyearalt end_fyearalt
gen yr1 = min(st_fyear ,st_fyearalt)
gen yrN = max(end_fyear,end_fyearalt)
drop st_fyear* end_fyear*
gen numyrs = yrN-yr1+1
expand numyrs
sort facilityid
gen year = yr1
bysort facilityid: replace year = year[_n-1]+1 if _n>1
gen hasloan=1
drop currency yr1 yrN facstart facend maturity
capture drop _merge
keep facilityid gvkey year
sort facilityid

/*merge onto lenders*/
joinby facilityid using 10_leads
sort facilityid companyid year
ren gvkey s_gvkey
keep s_gvkey year companyid
sort s_gvkey year companyid
tempfile sready
save `sready'

/*3) customer file of ds*/
clear
import excel using "DS_Links2018.xlsx", firstrow case(lower) sheet(link_data)

/*drop unnec vars*/
ren discrep_bcoid_flag linkflag
drop company coname_h fic score* smbl* ticker discrep*
sort gvkey
joinby gvkey using `scg'
ren facid facilityid
sort facilityid

tempfile prog2
save `prog2'
clear
use 00_facility 
gen year = substr(facilitystartdate,1,4)
destring year,replace
drop if year >2016
drop year

sort facilityid
joinby facilityid using `prog2'

/*fix facilitystartdate*/
gen _year = substr(facilitystartdate,1,4)
gen _mo = substr(facilitystartdate,6,2)
gen _day = substr(facilitystartdate,9,2)
destring _year _mo _day,replace
gen altst = mdy(_mo,_day,_year)
format altst %td
drop facilitystartdate _year _mo _day
ren altst facilitystartdate
/*fix facilityenddate*/
gen _year = substr(facilityenddate,1,4)
gen _mo = substr(facilityenddate,6,2)
gen _day = substr(facilityenddate,9,2)
destring _year _mo _day,replace
gen altst = mdy(_mo,_day,_year)
format altst %td
drop facilityenddate _year _mo _day
ren altst facilityenddate

count if facstartdate!=facilitystartdate 
order facstartdate facilitystart /*keep earlier*/
replace facstartdate = facilitystartdate if facilitystartdate<facstartdate

/*drop unnecessary vars*/
drop bcoid   facilitystartdate   loantype    secured     
gen facstart = mdy(month(facstartdate),1,year(facstartdate)) /*first day of mo to match the fyr-end data*/
format facstart %td
drop facstartdate
gen facend = mdy(month(facilityenddate),1,year(facilityenddate)) /*first day of mo to match the fyr-end data*/
format facend %td
drop facilityenddate

ren facstart date
ren gvkey c_gvkey
ren s_gvkey gvkey
merge m:1 gvkey date using `months'
/*for _merge==1, let's make fyear equal to calyear*/
replace fyear = year(date) if _merge==1
drop if _merge==2
drop _merge
ren date facstart
ren fyear st_fyear
ren fyearalt st_fyearalt

/*before merging in for facend, address issue of missing facend: first, replace with maturity-suggested end, when possible. 
	when maturity is missing too, just let facend = facstart; that way it just gets counted in one year*/
count if missing(facend) & !missing(maturity) 
tab maturity if missing(facend) & !missing(maturity)
gen altend = mofd(facstart)+maturity
format altend %tm
order facstart facend maturity altend
gen altalt =dofm(altend)
format altalt %td
order altalt,after(altend)
replace altend = altalt
drop altalt
replace facend = altend if missing(facend) /*first replace with mat-implied*/
replace facend = facstart if missing(facend) /*next replace with facstart, a.n.*/
drop altend

ren facend date
merge m:1 gvkey date using `months'
replace fyear = year(date) if _merge==1
drop if _merge==2
drop _merge
ren date facend
ren fyear end_fyear
ren fyearalt end_fyearalt
gen yr1 = min(st_fyear ,st_fyearalt)
gen yrN = max(end_fyear,end_fyearalt)
drop st_fyear* end_fyear*
gen numyrs = yrN-yr1+1
expand numyrs
sort facilityid gvkey
gen year = yr1
bysort facilityid gvkey: replace year = year[_n-1]+1 if _n>1
gen hasloan=1
drop currency yr1 yrN facstart facend maturity
capture drop _merge
ren gvkey s_gvkey
keep facilityid s_gvkey c_gvkey year
sort facilityid

/*merge onto lenders*/
joinby facilityid using 10_leads
keep s_gvkey year companyid c_gvkey
sort s_gvkey year companyid c_gvkey

/*4) merge them both*/
joinby s_gvkey year companyid using `sready',unmatched(both)
tab _merge
keep if _merge==3
sort c_gvkey s_gvkey year
by  c_gvkey s_gvkey year: gen num_commls = _N
by c_gvkey s_gvkey year: keep if _n==1
drop companyid _merge
sort s_gvkey c_gvkey year
gen commlen = 1
save 08_commons,replace








